package com.tizona.service.impl;

import com.tizona.dao.jdbc.FastStatement;
import com.tizona.dao.jdbc.ContractDAOJdbc;
import com.tizona.dao.ContractDAO;
import com.tizona.system.WU;
import com.tizona.TarifdConfig;
import com.tizona.entity.Lc;
import com.tizona.entity.Se;
import com.tizona.entity.Payment;
import com.tizona.entity.Invoice;
import com.tizona.service.*;

import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Date;
import java.util.HashSet;
import java.util.Calendar;
import java.util.Map;
import java.math.BigDecimal;

/**
 * Created by Vitaliy Kim.
 * Date: 17.08.2005
 * Time: 21:11:37
 */

public class ContractManagerImpl extends BaseManager implements ContractManager {

    FastStatement fstmtActiveContract, fstmtSessionBySE, fstmtStopSession, fstmtInsertRLog, fstmtTypeContract, fstmtSELastStart
            , fstmtSessionStatus, fstmtNewSession, fstmtSeIp, fstmtChunkRLOG, fstmtChunkRLOGCHN
            , fstmtAllContract, fstmtAllContractbyTp
            , fstmtTransactionContract, fstmtExtTransactionContract, fstmtGetTransactionsContract
            , fstmtContract, fstmtStartingSaldo, fstmtDebet, fstmtCredit
            , fstmtBulkPeriod, fstmtSetInvoice, fstmtDeleteUnprocessedRLog, fstmtDeleteUnprocessedTafidRLog;

    ContractDAO dao;

    public ContractManagerImpl(TarifdConfig cfg) throws Exception {

        this.cfg = cfg;

        //Code need implement to spring xml
        ContractDAO daojdbc = new ContractDAOJdbc();
        daojdbc.setRequest(this.cfg.sql);

        setDAO(daojdbc);

        fstmtTypeContract = cfg.sql.prepare("SELECT LC.LC_Num, SE.SE_ID\n" +
                "FROM SE, TECHN T, LC\n" +
                "WHERE LC.LC_Num = SE.LC_Num AND SE.TECHN_ID = T.TECHN_ID \n" +
                "AND SVC_ID = 3 AND T.TECHN_ID = 7 and LC.DELETED = 0 AND SE.DELETED = 0");

        fstmtSessionBySE = cfg.sql.prepare("SELECT SESSION_ID FROM SESSION_DETAILS SD, SE WHERE SD.Status <> 0 and SE.SE_ID = ?");

        fstmtStopSession = cfg.sql.prepare("UPDATE SESSION_DETAILS SET Stop = ?, Status = 0, Cause_Code = ? where SESSION_ID = ?");

        fstmtInsertRLog = cfg.sql.prepare("INSERT INTO R_LOG(SESSION_ID,TIMESTAMP,PROCESSED,DOWNLOAD,UPLOAD,TIME,CHN_ID) " +
                "VALUES(?, ?, ?, ?, ?, ?, ?)");

        fstmtSELastStart = cfg.sql.prepare("UPDATE SE set last_start = ? where SE_ID = ?");

        fstmtSessionStatus = cfg.sql.prepare("UPDATE SESSION_DETAILS SET Status = 0, Stop = ? where SE_ID = ? AND Status <> 0");

        fstmtNewSession = cfg.sql.prepare("INSERT INTO SESSION_DETAILS (SE_ID, HOST_ID, Line_NO, Caller_ID, \n" +
                " Called_ID, Speed, XSpeed, Remote_IP, Start, Stop, Cause_Code, Status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 1)");

        fstmtSeIp = cfg.sql.prepare("select * from IPTABLES where TYPE = 0 and MASK = 32 and SE_ID = ?");

        fstmtActiveContract = cfg.sql.prepare(
                "select distinct l.LC_Num,SALDO\n" +
                        "from LC l, SE s,SESSION_DETAILS sd\n" +
                        "where (s.DELETED=0)\n" +
                        "    and s.SE_ID =sd.SE_ID\n" +
                        "    and l.LC_Num = s.LC_Num\n" +
                        "    and (sd.SESSION_ID in (select SESSION_ID from TARIFD_R_LOG where PROCESSED=0))\n" +
                        "union\n" +
                        "select distinct l.LC_Num,SALDO\n" +
                        "from LC l, SE s,SESSION_DETAILS sd\n" +
                        "where (s.DELETED=0)\n" +
                        "    and s.SE_ID =sd.SE_ID\n" +
                        "    and l.LC_Num = s.LC_Num\n" +
                        "    and (sd.SESSION_ID in (select SESSION_ID from TARIFD_P_LOG))");


        fstmtAllContract = cfg.sql.prepare("select LC_Num, SALDO from LC where DELETED=0");

        fstmtAllContractbyTp = cfg.sql.prepare("select LC_Num, SALDO from LC where DELETED=0 and TC_ID = ?");

        fstmtContract = cfg.sql.prepare("select LC_Num, SALDO from LC where DELETED=0 and LC_Num=?");

        if(!(cfg.process_one_lc > 0))
        {
            fstmtChunkRLOG = cfg.sql.prepare(
                    "select RLM.R_ID, RL.SE_ID, RL.TIMESTAMP, RLM.TYPE, RLM.VALUE from " +
                            "TARIFD_R_LOG_MEASURE RLM, TARIFD_R_LOG RL " +
                            "where RL.R_ID=RLM.R_ID and RL.TIMESTAMP<=? and RL.PROCESSED=0");

            fstmtChunkRLOGCHN = cfg.sql.prepare(
                    "select RLCM.R_ID, RL.TIMESTAMP, RL.SE_ID, RLCM.TYPE, RLCM.VALUE, RLCM.CHN_ID from " +
                            "TARIFD_R_LOG RL, R_LOG_CHN_MEASURE RLCM where RL.TIMESTAMP<=? " +
                            "and RL.R_ID=RLCM.R_ID and RL.PROCESSED=0");
        }else
        {
            fstmtChunkRLOG = cfg.sql.prepare(
                    "select RLM.R_ID, RL.SE_ID, RL.TIMESTAMP, RLM.TYPE, RLM.VALUE from " +
                            "TARIFD_R_LOG_MEASURE RLM, TARIFD_R_LOG RL " +
                            "where RL.SE_ID in (select SE_ID from SE where LC_Num=" + cfg.process_one_lc + ") " +
                            "and RL.R_ID=RLM.R_ID and RL.TIMESTAMP<=? and RL.PROCESSED=0");

            fstmtChunkRLOGCHN = cfg.sql.prepare(
                    "select RLCM.R_ID, RL.TIMESTAMP, RL.SE_ID, RLCM.TYPE, RLCM.VALUE, RLCM.CHN_ID from " +
                            "TARIFD_R_LOG RL, R_LOG_CHN_MEASURE RLCM where RL.SE_ID in " +
                            "(select SE_ID from SE where LC_Num=" + cfg.process_one_lc + ") and " +
                            "RL.TIMESTAMP<=? " +
                            "and RL.R_ID=RLCM.R_ID and RL.PROCESSED=0");
        }

        fstmtTransactionContract = cfg.sql.prepare("sp_MakeTransContract ?,?,?,?,?,?");

        fstmtExtTransactionContract = cfg.sql.prepare("sp_MakeExtTransContract ?");

        fstmtGetTransactionsContract = cfg.sql.prepare("select TR_ID,CREDIT,DEBET from TRANSACTIONS where LC_Num=?");

        fstmtStartingSaldo = cfg.sql.prepare("sp_starting_saldo ?, ?");

        fstmtDebet = cfg.sql.prepare("select sum(DEBET) as DEBET from P_LOG " +
                "   where (REASON_CODE=1 or REASON_CODE=2 or REASON_CODE=3 or REASON_CODE=8 or REASON_CODE=20)" +
                "       and LC_Num=? and TIMESTAMP >= ? and TIMESTAMP < ?");

        fstmtCredit = cfg.sql.prepare("select sum(CREDIT) as CREDIT from P_LOG where " +
                "   (REASON_CODE=4 or REASON_CODE=6) " +
                "   and LC_Num=? and TIMESTAMP >= ? and TIMESTAMP < ?");

        fstmtBulkPeriod = cfg.sql.prepare("sp_bulk_by_period ?, ?");

        fstmtSetInvoice = cfg.sql.prepare(
                "INSERT INTO INVOICE (LC_Num,I_DATE,SSALDO,PAYMENT,COST,SALDO,PREPAYMENT," +
                        "ACCOUNT,DOWN,DOWN_FREE,UP,UP_FREE,ORD_N,ON_DATE,ACCOUNT_DATE) values"+
                        " (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

        fstmtDeleteUnprocessedRLog = cfg.sql.prepare("DELETE R_LOG FROM SE, SESSION_DETAILS D, R_LOG R \n" +
                "WHERE SE.SE_ID = D.SE_ID AND D.SESSION_ID = R.SESSION_ID AND SE.LC_Num = ? and SE.SVC_ID = 3 AND R.TIMESTAMP =< ?");

        fstmtDeleteUnprocessedTafidRLog = cfg.sql.prepare("DELETE TARIFD_R_LOG FROM SE, SESSION_DETAILS D, TARIFD_R_LOG R \n" +
                "WHERE SE.SE_ID = D.SE_ID AND D.SESSION_ID = R.SESSION_ID AND SE.LC_Num = ? and SE.SVC_ID = 3 AND R.TIMESTAMP =< ?");

    }

    protected void prepare(java.util.Date model_date) throws Exception {
        String model_date_dbf = WU.dateToDBF_ymdhms(model_date);
        String max_timestamp;

        log.debug("model_date_dbf: " + model_date_dbf);


        if(cfg.process_one_lc == -1L)
        {
            try
            {
                log.info("XXXStart copying new records ("+(new java.util.Date())+")");


                ResultSet res = cfg.sql.query("SELECT MIN(TIMESTAMP) FROM R_LOG WHERE PROCESSED=0");
                if (res.next() && res.getString(1)!=null) {
                    max_timestamp = res.getString(1);

                    String query, query2;

                    cfg.sql.update("begin transaction");

                    log.debug("max_timestamp: " + max_timestamp);

                    cfg.sql.update(
                            "DELETE TARIFD_SESSION_DETAILS FROM TARIFD_SESSION_DETAILS T, R_LOG R "+
                                    "WHERE T.SESSION_ID = R.SESSION_ID "+
                                    "AND R.PROCESSED = 0 "+
                                    "AND R.TIMESTAMP >= '"+ max_timestamp +"' "+
                                    "AND R.TIMESTAMP < '"+ model_date_dbf +"'");

                    cfg.sql.update(
                            "DELETE TARIFD_SESSION_DETAILS FROM TARIFD_SESSION_DETAILS S, TARIFD_P_LOG P " +
                                    "   WHERE S.SESSION_ID = P.SESSION_ID");

                    query = "INSERT INTO TARIFD_SESSION_DETAILS (SESSION_ID, SE_ID, HOST_ID, Line_NO, Caller_ID, \n " +
                            "   Called_ID, Remote_IP, Speed, XSpeed, Cause_Code, Start, Stop, Status)\n" +
                            "   SELECT DISTINCT S.SESSION_ID, S.SE_ID, S.HOST_ID, S.Line_NO, S.Caller_ID,\n " +
                            "       S.Called_ID, S.Remote_IP, S.Speed, S.XSpeed, S.Cause_Code, S.Start, S.Stop, S.Status\n" +
                            "       FROM SESSION_DETAILS S, R_LOG R\n" +
                            "       WHERE S.SESSION_ID = R.SESSION_ID\n" +
                            "       AND R.PROCESSED = 0\n" +
                            "       AND R.TIMESTAMP >= '"+ max_timestamp +"'\n" +
                            "       AND R.TIMESTAMP < '"+ model_date_dbf +"'\n" +
                            "UNION\n" +
                            "   SELECT DISTINCT S.SESSION_ID, S.SE_ID, S.HOST_ID, S.Line_NO, S.Caller_ID, \n " +
                            "       S.Called_ID, S.Remote_IP, S.Speed, S.XSpeed, S.Cause_Code, S.Start, S.Stop, S.Status\n" +
                            "       FROM SESSION_DETAILS S, TARIFD_P_LOG P\n" +
                            "       WHERE S.SESSION_ID = P.SESSION_ID";

//                    log.debug("insert session: " + query);
                    cfg.sql.update(query);

                    cfg.sql.update(
                            "INSERT INTO TARIFD_R_LOG (R_ID, CHN_ID, SESSION_ID, " +
                                    "TIMESTAMP, PROCESSED, TIME, DOWNLOAD,UPLOAD) "+
                                    "		  SELECT R_ID,"+
                                    "		   	     CHN_ID, "+
                                    "        	     SESSION_ID, "+
                                    "       		 TIMESTAMP, "+
                                    "       		 PROCESSED," +
                                    "                TIME, DOWNLOAD, UPLOAD "+
                                    "		  FROM R_LOG "+
                                    "		  WHERE (PROCESSED = 0) AND "+
                                    "             (TIMESTAMP >= '"+ max_timestamp +"') AND "+
                                    "             (TIMESTAMP < '"+ model_date_dbf +"')");

                    cfg.sql.update("DELETE FROM R_LOG "+
                            "WHERE (TIMESTAMP >= '"+ max_timestamp +"') AND "+
                            "      (TIMESTAMP < '"+ model_date_dbf +"')");

                    cfg.sql.update("commit transaction");
                    log.info("XXXEnd copying new records ("+(new java.util.Date())+")");
                    try {
                        FastStatement difference = cfg.sql.prepare(
                                "SELECT P.SESSION_ID, COALESCE(LC_Num, 0) AS LC_Num \n" +
                                        "   FROM TARIFD_P_LOG P \n" +
                                        "   WHERE SESSION_ID NOT IN (SELECT SESSION_ID FROM TARIFD_R_LOG \n" +
                                        "       WHERE (PROCESSED = 0) \n" +
                                        "       AND (TIMESTAMP >= '"+ max_timestamp +"') "+
                                        "       AND (TIMESTAMP < '"+ model_date_dbf +"'))");
                        ResultSet diff = difference.query();
                        while(diff.next()) {
                            log.info("Session_Id:" + diff.getString("SESSION_ID")
                                    + " LC_Num:"+diff.getString("LC_Num") + " does NOT have active chunks");
                        }
                        diff.close();
                    } catch (SQLException ex) {
                        // just ignore
                    }
                }
                res.close();
            }catch (Exception e)
            {
                cfg.sql.update("rollback transaction");
                e.printStackTrace();
                log.error("XXXError:" + e.getMessage());
                throw new Exception(e);
            }
        }

    }

    /**
     *
     * @param modelTime Model time or time when starting cycle process
     */
    public void handleActiveContracts(Calendar modelTime) {

        try {

            prepare(modelTime.getTime());

            log.info("STARTING HANDLE ACTIVE CONTRACTS");

            handleContracts(modelTime, fstmtActiveContract);

            System.gc();
        } catch (Exception e) {
            log.error("Can't handle active Contracts: " + e.getMessage());
            e.printStackTrace();
        }

    }

    //todo collocation start

    /**
     *
     * @param modelTime Model time or time when starting the cycle process
     */
    public void handleCollocationContracts (Calendar modelTime) {
        try {
            ResultSet col_res = cfg.sql.query(fstmtTypeContract);
            while (col_res.next()) {
                try {
                    processCollocationContract(col_res.getLong(2), modelTime);
                } catch (Exception e) {
                    log.error("Can't handle collocationCONTRACT: " + col_res.getLong(1) + " with exception: " + e);
                    e.printStackTrace();
                }

            }
        } catch (Exception e) {
            log.error("Can't handle collocationType Contracts: " + e.getMessage());
            e.printStackTrace();
        }

    }

    private void processCollocationContract(long se_id, Calendar modelTime) {
        // stop all the current sessions
        log.debug("Stopping all the sessions for SE: " + se_id);
        collocationContractStopSession(se_id, modelTime);

        // start a new session for each contract
        log.debug("Starting a new session for SE: " + se_id);
        collocationContractStartSession(se_id, modelTime);

        cfg.sql.closeStmtAndRs();

        log.debug("success: DONE PROCESSING COLLOCATION");
    }

    private void collocationContractStopSession(long se_id, Calendar modelTime) {
        try {
            fstmtSessionBySE.setLong(1, se_id);
            ResultSet res = cfg.sql.query(fstmtSessionBySE);
            while(res.next()) {

                fstmtStopSession.setTimestamp(1, new Timestamp(modelTime.getTimeInMillis()));
                fstmtStopSession.setInt(2, 6);
                fstmtStopSession.setLong(3, res.getLong("SESSION_ID"));
                cfg.sql.update(fstmtStopSession);

                fstmtInsertRLog.setLong(1, res.getLong("SESSION_ID"));
                fstmtInsertRLog.setTimestamp(2, new Timestamp(modelTime.getTimeInMillis()));
                fstmtInsertRLog.setInt(3, 0);
                fstmtInsertRLog.setLong(4, 0);
                fstmtInsertRLog.setLong(5, 0);
                fstmtInsertRLog.setLong(6, 0);
                fstmtInsertRLog.setInt(7, 1);

                cfg.sql.update(fstmtInsertRLog);
            }

        } catch(Exception ex) {
            log.error("Can't handle collocationType Contract StopSession se_id = " + se_id + ": " + ex.getMessage());
            ex.printStackTrace();

        }
    }

    private void collocationContractStartSession(long se_id, Calendar modelTime) {
        try {

            // set SE last_start to the current date
            fstmtSELastStart.setLong(1, (modelTime.getTimeInMillis() / 1000));
            fstmtSELastStart.setLong(2, se_id);

            cfg.sql.update(fstmtSELastStart);

            // update SESSION_DETAILS Status (just in case)
            fstmtSessionStatus.setTimestamp(1, new Timestamp(modelTime.getTimeInMillis()));
            fstmtSessionStatus.setLong(2, se_id);
            
            cfg.sql.update(fstmtSessionStatus);

            // Create a new session

            // Get the SE stat-IP
            String seIp = "0.0.0.0";
            fstmtSeIp.setLong(1, se_id);
            ResultSet res1 = cfg.sql.query(fstmtSeIp);

            if(res1.next()) {
                seIp = res1.getString("IPAddress");
            }

            cfg.sql.update("INSERT INTO SESSION_DETAILS (SE_ID, HOST_ID, Line_NO, Caller_ID, \n" +
                    " Called_ID, Speed, XSpeed, Remote_IP, Start, Stop, Cause_Code, Status) " +
                    " VALUES (" + se_id + ", 25000008, 15, '', '', null, null, '" + seIp + "', getDate(), getDate(), 0, 1)");

            ResultSet res2 = cfg.sql.query("SELECT @@identity");
            if(res2.next()) {
                fstmtInsertRLog.setLong(1, res2.getLong(1));
                fstmtInsertRLog.setTimestamp(2, new Timestamp(modelTime.getTimeInMillis()));
                fstmtInsertRLog.setInt(3, 0);
                fstmtInsertRLog.setLong(4, 0);
                fstmtInsertRLog.setLong(5, 0);
                fstmtInsertRLog.setLong(6, 0);
                fstmtInsertRLog.setInt(7, 1);

                cfg.sql.update(fstmtInsertRLog);
            }
            res1.clearWarnings();
            res1.close();

            res2.clearWarnings();
            res2.close();

        } catch(Exception ex) {
            log.error("Can't handle collocationType Contract StartSession se_id = " + se_id + ": " + ex.getMessage());
            ex.printStackTrace();

        }
    }

    public void handleAllContracts(Calendar modelTime) {

        try {

            log.info("XXX STARTING HANDLE ALL CONTRACTS");
            handleContracts(modelTime, fstmtAllContract);

        } catch (Exception e) {
            log.error("Can't handle all Contracts: " + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     *
     * @param modelTime
     * @param tp - TC_ID
     */
    public void handleAllContracts(Calendar modelTime, long tp) {

        try {

            if(tp == 0 ) return;

            fstmtAllContractbyTp.setLong(1, tp);

            log.info("XXX STARTING HANDLE ALL CONTRACTS BY TC_ID = " + tp);
            handleContracts(modelTime, fstmtAllContractbyTp);

        } catch (Exception e) {
            log.error("Can't handle all Contracts: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private void handleContracts(Calendar modelTime, FastStatement fstmta) throws Exception {
        {

            ResultSet alc_res;

            alc_res = cfg.sql.query(fstmta);

            while (alc_res.next()) {

                try {

                    processContract(alc_res.getLong(1), alc_res.getBigDecimal("SALDO"), modelTime);

                } catch (Exception e) {
                    log.error("Can't handle CONTRACT: " + alc_res.getLong(1));
                    e.printStackTrace();
                }

            }
        }
    }

    private void processContract(Long lc_num, BigDecimal saldo, Calendar modelTime) throws Exception {

        ServiceManager serviceMng = cfg.serviceMan;

        Lc lc = new Lc(lc_num, saldo);

        log.info("\t\t###### HANDLE CONTRACT: " + lc.getLcNum());

        HashSet <Se> ses = serviceMng.getActiveServices(lc, modelTime);
        lc.setSes(ses);

        processTariffication(lc, modelTime);

    }

    /**
     * For static process via Interface of command line
     * @param modelTime
     * @param lc_num
     */
    public void handleStaticContract(Calendar modelTime, long lc_num) {

        ResultSet lc_res;
        ServiceManager serviceMng = getServiceManager();

        try {

            fstmtContract.setLong(1, lc_num);
            lc_res = cfg.sql.query(fstmtContract);
            if(lc_res.next()) {

                log.info("\t\tSTARTING HANDLE CONTRACT");

                Lc lc = new Lc(lc_res.getLong(1), lc_res.getBigDecimal("SALDO"));
                log.debug("Handle contract: " + lc.getLcNum());

                fstmtGetTransactionsContract.setLong(1, lc_res.getLong(1));
                ResultSet trans = cfg.sql.query(fstmtGetTransactionsContract);
                int number_transactin = 0;
                log.debug("saldo before transactions: " + lc.getSaldo());

                while(trans.next()) {
                    makeExternalTransaction(trans.getLong(1), trans.getBigDecimal("CREDIT"), trans.getBigDecimal("DEBET"), lc) ;
                    ++number_transactin;
                }

                log.debug("Number transaction: " + number_transactin
                        + "CURRENT SALDO after transactions = " + lc.getSaldo());

                HashSet <Se> ses = serviceMng.getServices(lc, modelTime);
                lc.setSes(ses);

                processTariffication(lc, modelTime);

            }


        } catch (Exception e) {
            log.error("Can't handle Static Contract: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private void processTariffication(Lc lc, final Calendar modelTime) throws Exception {

        ServiceManager serviceMng = getServiceManager();

        dao.loadParams(lc);
        loadAccumulators(lc);
        log.debug("Starting tarification ...");

        serviceMng.process(lc.getSes(), modelTime);

        //Make Transactions for contract
        log.debug("Pays for contract: " + lc.getPays().size());
        makeTransactions(lc);

        //Generate Invoice
        if(lc.getInvoice()!= null) {
            log.info("GENERATE INVOICE");
            setInvoice(lc.getInvoice());
        }

        updateAccumulators(lc, modelTime);
        updateParams(lc);

        for(Se se : lc.getSes())
            if(se.getSvc() == 3)
                updateParams(se);

    }


    /**
     * Only one valuable var - either VALUE or DATE!!
     * @param lc
     * @throws SQLException
     */
    private void updateParams(Lc lc) throws SQLException {

        for (Map.Entry element : lc.getLcProps().entrySet()) {
            com.tizona.entity.tarification.Parameter prop = (com.tizona.entity.tarification.Parameter) element.getValue();
            if (prop.isChanged()) {
                String key = (String) element.getKey();
                if (prop.isCreate()) {

                    dao.createProp(lc, key, prop);

                } else {

                    if(dao.updateProp(lc, key, prop)==0) {
                        dao.createProp(lc, key, prop);
                    }
                }

            }
        }
    }

    /**
     * Only one valuable var - either VALUE or DATE!!
     * @param se
     * @throws SQLException
     */
    private void updateParams(Se se) throws SQLException {

        for (Map.Entry element : se.getSeProps().entrySet()) {
            com.tizona.entity.tarification.Parameter prop = (com.tizona.entity.tarification.Parameter) element.getValue();
            if (prop.isChanged()) {
                String key = (String) element.getKey();
                if (prop.isCreate()) {

                    dao.createProp(se, key, prop);

                } else {

                    if(dao.updateProp(se, key, prop)==0) {
                        dao.createProp(se, key, prop);
                    }
                }

            }
        }
    }

    public void updateAccumulators(Lc lc, final Calendar modelTime) throws SQLException {

        dao.updateAccumulators(lc, modelTime);

    }

    public void loadAccumulators(Lc lc) throws SQLException {

        dao.loadAccumulators(lc);

    }

    private void makeTransaction(TransactionContract tran) throws SQLException {

        fstmtTransactionContract.setLong(1, tran.getLc().getLcNum());
        fstmtTransactionContract.setTimestamp(2, new Timestamp(tran.getPay().getTimestamp().getTime()));
        fstmtTransactionContract.setDouble(3, tran.getPay().getDebet());
        fstmtTransactionContract.setDouble(4, 0.0);
        fstmtTransactionContract.setInt(5, tran.getPay().getReason_code());
        fstmtTransactionContract.setString(6, tran.getPay().getReason_text());
        cfg.sql.update(fstmtTransactionContract);

    }

    public void makeTransactions(Lc lc) throws SQLException {

        log.debug("Pays for contract: " + lc.getPays().size());
        for (Payment pay : lc.getPays()) {
            TransactionContract tran = new TransactionContractImpl();
            tran.setLc(lc);
            tran.setPay(pay);
            makeTransaction(tran);
        }

        lc.getPays().clear();

    }

    public void deleteOldChunks(final Lc lc, Calendar abon_time) throws SQLException {
        log.debug("Deleting old chunks from R_LOG");
        fstmtDeleteUnprocessedRLog.setLong(1, lc.getLcNum());
        fstmtDeleteUnprocessedRLog.setTimestamp(2, new Timestamp(abon_time.getTimeInMillis()));
        fstmtDeleteUnprocessedRLog.update();

        log.debug("Deleting old chunks from TARIFD_R_LOG");
        fstmtDeleteUnprocessedTafidRLog.setLong(1, lc.getLcNum());
        fstmtDeleteUnprocessedTafidRLog.setTimestamp(2, new Timestamp(abon_time.getTimeInMillis()));
        fstmtDeleteUnprocessedTafidRLog.update();

    }

    /**
     *
     * @param tran
     * @param debet
     * @param debet
     * @param lc
     * @throws SQLException
     */
    private void makeExternalTransaction(Long tran, BigDecimal credit, BigDecimal debet,Lc lc) throws SQLException {

        fstmtExtTransactionContract.setLong(1, tran);
        cfg.sql.update(fstmtExtTransactionContract);
        lc.setSaldo(lc.getSaldo().subtract(debet).add(credit));
    }

    /**
     * Procedure calculate based on Stored Procedure sp_starting_saldo
     *
     * @param date  DateTime on current month with need starting saldo
     * @return saldo
     */
    public double getStartingSaldo(Lc lc, Calendar date) throws SQLException
    {
        long dt = date.getTimeInMillis();
        fstmtStartingSaldo.setLong(1, lc.getLcNum());
        fstmtStartingSaldo.setDate(2, new java.sql.Date(dt));

        log.debug(fstmtStartingSaldo.getSQLString());

        ResultSet resultset = cfg.sql.query(fstmtStartingSaldo);
        if(resultset.next())
            return resultset.getDouble("saldo");
        else return 0d;
    }

    public double getDebet(Lc lc, Calendar start, Calendar stop) throws SQLException
    {
        long st = start.getTimeInMillis(), sp = stop.getTimeInMillis();
        double debet = 0d;
        fstmtDebet.setBigDecimal(1, new BigDecimal(lc.getLcNum()));
        fstmtDebet.setTimestamp(2, new Timestamp(st));
        fstmtDebet.setTimestamp(3, new Timestamp(sp));

        ResultSet resultset = cfg.sql.query(fstmtDebet);
        if(resultset.next()) {
            debet = resultset.getDouble("DEBET");
        }

        return debet;
    }

    public void setAmountOfContract(Invoice invoice, Calendar stopTime) throws SQLException {
        long stop = stopTime.getTimeInMillis();

        fstmtBulkPeriod.setBigDecimal(1, new BigDecimal(invoice.getLc().getLcNum()));
        fstmtBulkPeriod.setTimestamp(2, new Timestamp(stop));

        ResultSet resultset = cfg.sql.query(fstmtBulkPeriod);
        if(resultset.next()) {
            invoice.setCOST(resultset.getDouble("DEBET"));
            invoice.setPAYMENT(resultset.getDouble("CREDIT"));
        }

    }

    public double getCredit(Lc lc, Calendar start, Calendar stop) throws SQLException {

        long st = start.getTimeInMillis(), sp = stop.getTimeInMillis();
        double credit = 0d;
        fstmtCredit.setBigDecimal(1, new BigDecimal(lc.getLcNum()));
        fstmtCredit.setTimestamp(2, new Timestamp(st));
        fstmtCredit.setTimestamp(3, new Timestamp(sp));

        ResultSet resultset = cfg.sql.query(fstmtCredit);
        if(resultset.next())
            credit = resultset.getDouble("CREDIT");

        log.debug(fstmtCredit.getSQLString());

        return credit;
    }

    public void setInvoice(Invoice invc) throws SQLException {

        long idate = invc.getI_DATE().getTimeInMillis(),
                ondate = invc.getON_DATE().getTimeInMillis(),
                ac_date = invc.getACCOUNT_DATE().getTimeInMillis();

        fstmtSetInvoice.setLong(1, invc.getLc().getLcNum());
        fstmtSetInvoice.setTimestamp(2, new java.sql.Timestamp(idate));
        fstmtSetInvoice.setDouble(3, invc.getSSALDO());
        fstmtSetInvoice.setDouble(4, invc.getPAYMENT());
        fstmtSetInvoice.setDouble(5, invc.getCOST());
        fstmtSetInvoice.setDouble(6, invc.getSALDO());
        fstmtSetInvoice.setDouble(7, invc.getPREPAYMENT());
        fstmtSetInvoice.setDouble(8, invc.getACCOUNT());
        fstmtSetInvoice.setLong(9, invc.getDOWN());
        fstmtSetInvoice.setLong(10, invc.getDOWN_FREE());
        fstmtSetInvoice.setLong(11, invc.getUP());
        fstmtSetInvoice.setLong(12, invc.getUP_FREE());
        fstmtSetInvoice.setLong(13, invc.getORD_N());
        fstmtSetInvoice.setTimestamp(14, new java.sql.Timestamp(ondate));
        fstmtSetInvoice.setTimestamp(15, new java.sql.Timestamp(ac_date));

        cfg.sql.update(fstmtSetInvoice);
    }

    public void setDAO(ContractDAO lcDAO) {
        this.dao = lcDAO;
    }



}
